﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;
using System.Configuration;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class ItsmEventType : BasicITSM, IItsmEventType
    {
        private readonly static string connstr = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
        private const string sqlInsert = "insert into fj_hpd_help_desk_eventtype (SUBMITTER,CREATE_DATE,LAST_MODIFIED_BY,MODIFIED_DATE,STATUS,EVENT_PROPERTIES,TIER1,TIER2,TIER3,CINT_DISABLED) values('Demo','1381472665','Demo','1381472665',0,:EVENT_PROPERTIES,:TIER1,:TIER2,:TIER3,0)";
        private const string sqlDelete = "delete from fj_hpd_help_desk_eventtype where id=:id";
        private const string sqlGetEventbyName = "select * from fj_hpd_help_desk_eventtype where tier2=:tier2 and tier3=:tier3";
        private const string sqlupdate = "update fj_hpd_help_desk_eventtype set tier3=:tier3 where ID=:id";
        private const string sqlAllEvent = "select * from fj_hpd_help_desk_eventtype where cint_disabled!=1";

        public List<EventTypeModel> GetAllEvent()
        {
            return ModelHelper<EventTypeModel>.GetModel_By_DirectSql(sqlAllEvent, null, oda);
        }

        public EventTypeModel GetEventByName(string tier2, string tier3)
        {
            EventTypeModel model = new EventTypeModel();

            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = @"select * from fj_hpd_help_desk_eventtype where tier2=:tier2 and tier3=:tier3";
                var t1 = new OracleParameter("tier2", OracleDbType.NVarchar2, 100);
                t1.Value = tier2;
                var t2 = new OracleParameter("tier3", OracleDbType.NVarchar2, 100);
                t2.Value = tier3;
                com.Parameters.Add(t1);
                com.Parameters.Add(t2);

                using (var reader = com.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["REQUEST_ID"] != DBNull.Value) model.REQUEST_ID = (string)reader["REQUEST_ID"];
                        if (reader["TIER1"] != DBNull.Value) model.TIER1 = (string)reader["TIER1"];
                        if (reader["TIER2"] != DBNull.Value) model.TIER2 = (string)reader["TIER2"];
                        if (reader["TIER3"] != DBNull.Value) model.TIER3 = (string)reader["TIER3"];
                        if (reader["EVENT_PROPERTIES"] != DBNull.Value) model.EVENT_PROPERTIES = (string)reader["EVENT_PROPERTIES"];
                        if (reader["CINT_DISABLED"] != DBNull.Value) model.CINT_DISABLED = (Int64)reader["CINT_DISABLED"];
                    }
                }
            }
            return model;
        }

        public EventTypeModel GetEventByID(string id)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                    new QueryParameter(){ 
                        OracleDbColumnName="REQUEST_ID", 
                        OracleDbColumnType=OracleDbType.NVarchar2,
                        OracleDbColumnValue=id,
                        QueryOperator=QueryOperatorType.equal,
                        PredicateType=PredicateType.where
                    },
            };
            return ModelHelper<EventTypeModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
        }

        public int Add(EventTypeModel model)
        {
            int result = 0;
            try
            {
                using (OracleConnection conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    OracleCommand com = new OracleCommand();
                    this.PrepareCommand(com, conn, null, CommandType.Text, sqlInsert, new List<OracleParameter>() 
                        { 
                            new OracleParameter("EVENT_PROPERTIES", OracleDbType.NVarchar2, 100, model.EVENT_PROPERTIES, ParameterDirection.Input),
                            new OracleParameter("TIER1", OracleDbType.NVarchar2, 100, model.TIER1, ParameterDirection.Input),
                            new OracleParameter("TIER2", OracleDbType.NVarchar2, 100, model.TIER2, ParameterDirection.Input),
                            new OracleParameter("TIER3", OracleDbType.NVarchar2, 100, model.TIER3, ParameterDirection.Input)
                        });
                    com.ExecuteNonQuery();
                }
                result = 1;
            }
            catch (Exception e)
            {
                result = 0;
            }
            return result;
        }

        public void Delete(string ID)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(connstr))
                {
                    conn.Open();
                    OracleCommand com = new OracleCommand();
                    this.PrepareCommand(com, conn, null, CommandType.Text, sqlDelete, new List<OracleParameter>() 
                        { 
                            new OracleParameter("id", OracleDbType.NVarchar2, 100, ID, ParameterDirection.Input)
                        });
                    com.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
            }
        }

        public int Update(EventTypeModel model)
        {
            int result = 0;
            if (model != null)
            {
                try
                {
                    using (OracleConnection conn = new OracleConnection(connstr))
                    {
                        conn.Open();
                        OracleCommand com = new OracleCommand();
                        this.PrepareCommand(com, conn, null, CommandType.Text, sqlupdate, new List<OracleParameter>() 
                        { 
                            new OracleParameter("EVENT_PROPERTIES", OracleDbType.NVarchar2, 100, model.EVENT_PROPERTIES, ParameterDirection.Input),
                            new OracleParameter("TIER1", OracleDbType.NVarchar2, 100, model.TIER1, ParameterDirection.Input),
                            new OracleParameter("TIER2", OracleDbType.NVarchar2, 100, model.TIER2, ParameterDirection.Input),
                            new OracleParameter("TIER3", OracleDbType.NVarchar2, 100, model.TIER3, ParameterDirection.Input),
                            new OracleParameter("CINT_DISABLED", OracleDbType.Decimal, 100, 0, ParameterDirection.Input),
                        });
                        com.ExecuteNonQuery();
                    }
                    result = 1;
                }
                catch (Exception e)
                {
                    result = 0;
                }
            }
            return result;
        }

        public bool GetExistEvent(string tier2, string tier3)
        {
            bool result = false;
            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                OracleCommand com = conn.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText = @"select * from fj_hpd_help_desk_eventtype where tier2=:tier2 and tier3=:tier3";
                var t1 = new OracleParameter("tier2", OracleDbType.NVarchar2, 100);
                t1.Value = tier2;
                var t2 = new OracleParameter("tier3", OracleDbType.NVarchar2, 100);
                t2.Value = tier3;
                com.Parameters.Add(t1);
                com.Parameters.Add(t2);

                using (var reader = com.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
            }
            return result;
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2)
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }

        /// <summary>
        /// 今日新增工单
        /// </summary>
        /// <param name="company"></param>
        /// <param name="total"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<ToDoModel> GetWorkOrderCreateInToday(string company, out decimal total, int page, int rows, string sort = "SubmitDate", string order = "Descending")
        {
            var result = new List<ToDoModel>();
            //0---order by
            //1---company
            //2---rn <=10 and rn >=1  分页
            var basicSql = @"select rownum as rn,t.* from (
                            (select 
                            Entry_ID as RequestID,
                            Incident_Number as OrderID,
                            '事件' as TypeS,
                            Description,
                            case status when 0 then '新建' when 1 then '已指派' when 2 then '处理中' when 3 then '暂停' when 4 then '已解决' when 5 then '已关闭' else '其他' end as Status,
                            Assignee as Assigner,
                            cChr_SubmitterFullName as Submitter,
                            to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as SubmitDate,
                            Company
                            from hpd_help_desk)
                             union all 
                            (select 
                            Request_ID as RequestID,
                            Infrastructure_Change_ID as OrderID,
                            '变更' as TypeS,
                            Description,
                            case Change_Request_Status when 0 then '已提交' when 1 then '已设计' when 2 then '变更请求' when 3 then '进行中的计划' when 7 then '正在执行' when 8 then '暂停' when 9 then '已拒绝' when 10 then '已完成' when 11 then '已关闭' when 12 then '已取消' else '' end as Status,
                            ASCHG as Assigner,
                            cChr_SubmitterFullName as Submitter,
                            to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as SubmitDate,
                            Company
                            from CHG_Infrastructure_Change)
                            union all 
                            (select 
                            sys_problem_investigation_id as RequestID,
                            Problem_Investigation_ID as OrderID,
                            '问题' as TypeS,
                            Description,
                            case Investigation_Status WHEN 0 THEN '草稿'  WHEN 1 THEN '评审中' WHEN 2 THEN '授权请求' WHEN 3 THEN '已指派' WHEN 4 THEN '调查中' WHEN 5 THEN '暂停' when 6 then '已完成' when 7 then '已拒绝' when 8 then '已关闭' when 9 then '已取消' ELSE '其他' END as Status,   
                            Assignee as Assigner,
                            cChr_SubmitterFullName as Submitter,
                            to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as SubmitDate,
                            Company
                            from pbm_problem_investigation) {0} ) t 
                            where 
                            SubmitDate >= to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')
                            and SubmitDate <= to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')+0.99999
                            and company='{1}'";
            var sql = @"select * from ( " + basicSql + " )  {2} ";
            var countSql = "select count(*) as Count from ( " + basicSql + " )";

            int beginIndex = (page - 1) * rows + 1;
            int endIndex = beginIndex + rows - 1;

            var orderName = " asc ";
            if (!string.IsNullOrWhiteSpace(order))
            {
                if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    orderName = " desc ";
                }
            }

            sql = string.Format(sql, " order by " + sort + " " + orderName, company, " where rn between " + beginIndex + " and " + endIndex + "");
            countSql = string.Format(countSql, "", company);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    result = AutoMapper.Map<OracleDataReader, ToDoModel>(dr);
                    total = ModelHelper<ToDoModel>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                    dr.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// 今日之前未关闭工单
        /// </summary>
        /// <param name="company"></param>
        /// <param name="total"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<ToDoModel> GetNotClosedWorkOrderBeforeToday(string company, out decimal total, int page, int rows, string sort = "SubmitDate", string order = "Descending")
        {
            var result = new List<ToDoModel>();

            var basicSql = @"select rownum as rn,t.* from (
                            (select   
                            Entry_ID as RequestID, 
                            Incident_Number as OrderID,
                            '事件' as TypeS,   
                            Description,   
                            case status when 0 then '新建' when 1 then '已指派' when 2 then '处理中' when 3 then '暂停' when 4 then '已解决' when 5 then '已关闭' else '其他' end as Status,
                            Assignee as Assigner, 
                            cChr_SubmitterFullName as Submitter,    
                            to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as SubmitDate,      
                            Company             
                            from hpd_help_desk where status < 5)       
                            union all           
                            (select            
                            Request_ID as RequestID,  
                            Infrastructure_Change_ID as OrderID,    
                            '变更' as TypeS,       
                            Description,            
                            case Change_Request_Status when 0 then '已提交' when 1 then '已设计' when 2 then '变更请求' when 3 then '进行中的计划' when 7 then '正在执行' when 8 then '暂停' when 9 then '已拒绝' when 10 then '已完成' when 11 then '已关闭' when 12 then '已取消' else '' end as Status,
                            ASCHG as Assigner,         
                            cChr_SubmitterFullName as Submitter,    
                            to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as SubmitDate,       
                            Company                   
                            from CHG_Infrastructure_Change where Change_Request_Status < 11)             
                            union all                
                            (select                       
                            sys_problem_investigation_id as RequestID, 
                            Problem_Investigation_ID as OrderID,  
                            '问题' as TypeS,              
                            Description,                 
                            case Investigation_Status WHEN 0 THEN '草稿'  WHEN 1 THEN '评审中' WHEN 2 THEN '授权请求' WHEN 3 THEN '已指派' WHEN 4 THEN '调查中' WHEN 5 THEN '暂停' when 6 then '已完成' when 7 then '已拒绝' when 8 then '已关闭' when 9 then '已取消' ELSE '其他' END as Status,   
                            Assignee as Assigner,     
                            cChr_SubmitterFullName as Submitter,     
                            to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Submit_Date+28800,'second') as SubmitDate,  
                             Company       
                             from pbm_problem_investigation where Investigation_Status < 8)  {0} ) t   
                             where  
                             SubmitDate < to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')       
                             and company='{1}'";

            var sql = @"select * from ( " + basicSql + " )  {2}";
            var countSql = "select count(*) as Count from ( " + basicSql + " )";

            int beginIndex = (page - 1) * rows + 1;
            int endIndex = beginIndex + rows - 1;

            var orderName = " asc ";
            if (!string.IsNullOrWhiteSpace(order))
            {
                if (order.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    order.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    orderName = " desc ";
                }
            }

            sql = string.Format(sql, " order by " + sort + " " + orderName, company, " where rn between " + beginIndex + " and " + endIndex + "");
            countSql = string.Format(countSql, "", company);

            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                using (OracleDataReader dr = conitsm.ExecuteReader(sql, null))
                {
                    result = AutoMapper.Map<OracleDataReader, ToDoModel>(dr);
                    total = ModelHelper<ToDoModel>.GetModelCount_By_DirectSql(countSql, null, conitsm);
                    dr.Close();
                }
            }

            return result;
        }
    }
}
